package com.tte.common.utils.office;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;


/**
 * Excel工具类:
 * 2003版本(后缀是.xls)和2007版本(后缀名是.xlsx)存在兼容性的问题！03最多只有65536行！07版本最多有1048576行！
 * 工作簿（Workbook）、工作表（Sheet）、行（Row）、列 （Cell）
 */
@Component
@Slf4j
public class ExceltUtils {

    /**
     * 第一步：创建Excel
     * @return:
     */
    public static XSSFWorkbook createXSSFWorkbook() throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        return workbook;
    }

    /**
     * 第二步：创建sheet
     * @param workbook
     * @param sheetName
     * @return
     * @throws IOException
     */
    public static XSSFSheet createSheel(XSSFWorkbook workbook, String sheetName) throws IOException {
        XSSFSheet sheet = workbook.createSheet(sheetName);
        return sheet;
    }

    /**
     * 第三步：创建sheet
     * @param sheet
     * @param rowNum
     * @return
     * @throws IOException
     */
    public static Row createRow(XSSFSheet sheet, int rowNum) throws IOException {
        Row row = sheet.createRow(rowNum);
        return row;
    }

    /**
     * 第四步：创建Cell
     * @param row
     * @param cellNum
     * @return
     * @throws IOException
     */
    public static Cell createCell(Row row, int cellNum) throws IOException {
        Cell cell = row.createCell(cellNum);
        return cell;
    }

    public static Cell createCell(Row row, int cellNum, String cellData) throws IOException {
        Cell cell = row.createCell(cellNum);
        cell.setCellValue(cellData);
        return cell;
    }

    public static Cell createCell(Row row, int cellNum, String cellData, CellStyle style) throws IOException {
        Cell cell = row.createCell(cellNum);
        cell.setCellValue(cellData);
        cell.setCellStyle(style);
        return cell;
    }

    public static void createTitleRow(XSSFSheet sheet, int rowNum, String[] titleStr) throws IOException {
        Row row = sheet.createRow(rowNum);
        for (int j = 0; j < titleStr.length; j++) {
            Cell cell = row.createCell(j);
            cell.setCellValue(titleStr[j]);
        }
    }

    public static void createTitleRow(XSSFSheet sheet, int rowNum, String[] titleStr, CellStyle style) throws IOException {
        Row row = sheet.createRow(rowNum);
        for (int j = 0; j < titleStr.length; j++) {
            Cell cell = row.createCell(j);
            cell.setCellValue(titleStr[0]);
            cell.setCellStyle(style);
        }
    }


    public static void createDataRow(XSSFSheet sheet, int rowNum, String[] cellIndex, List<Map<String, String>> data) throws IOException {
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(rowNum + i + 1);
            Map<String, String> romData = data.get(i);
            for (int j = 0; j < cellIndex.length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(romData.get(cellIndex[j]));
            }
        }
    }


    /**
     * 合并表格,坐标从0开始
     *
     * @param sheet
     * @param firstRow 起始行
     * @param lastRow  结束行
     * @param firstCol 起始列
     * @param lastCol  结束列
     */
    public static void merged(XSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        // 2、
        // 前两个参数表示合并的起始行和结束行，后两个参数表示合并的起始列和结束列
        // 示例代码表示合并第一行的第一到第七列
        // CellRangeAddress region = new CellRangeAddress(0, 0, 0, 6);
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);
    }

    public static Sheet getSheet(Workbook wb, String sheetName) {
        return wb.getSheet(sheetName);
    }

    public static Sheet getSheet(Workbook wb, int index) {
        return wb.getSheetAt(index);
    }


    /**
     * 定义表头样式
     *
     * @param xssfWorkbook
     * @return
     */
    public static CellStyle getTitileStyle(XSSFWorkbook xssfWorkbook) {
        XSSFCellStyle cs = xssfWorkbook.createCellStyle();
        cs.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
        cs.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
        cs.setBorderBottom(BorderStyle.THIN);     //设置下边框
        cs.setBorderTop(BorderStyle.THIN);        //设置上边框
        cs.setBorderLeft(BorderStyle.THIN);       //设置左边框
        cs.setBorderRight(BorderStyle.THIN);      //设置右边框

        // 设置背景填充色
        cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        cs.setFillForegroundColor(new XSSFColor(new Color(180, 180, 180)));
        // 设置字体
        XSSFFont font = xssfWorkbook.createFont();
        font.setBold(true);
        cs.setFont(font);
        return cs;
    }

    /**
     * 定义数据单元格样式
     */
    public static XSSFCellStyle getDataStyle(XSSFWorkbook workbook) {
        // 数据单元格样式
        XSSFCellStyle dataCS = workbook.createCellStyle();
        dataCS.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
        dataCS.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
        dataCS.setBorderBottom(BorderStyle.THIN);     //设置下边框
        dataCS.setBorderTop(BorderStyle.THIN);        //设置上边框
        dataCS.setBorderLeft(BorderStyle.THIN);       //设置左边框
        dataCS.setBorderRight(BorderStyle.THIN);      //设置右边框
        dataCS.setWrapText(true); // 设置自动换行显示
        return dataCS;
    }


    /**
     * 定义 Total单元格样式
     */
    public static XSSFCellStyle getTotalStyle(XSSFWorkbook workbook) {
        // 汇总单元格样式
        XSSFCellStyle totalCS = workbook.createCellStyle();
        totalCS.setAlignment(HorizontalAlignment.CENTER);        // 水平居中
        totalCS.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
        totalCS.setBorderBottom(BorderStyle.THIN);     //设置下边框
        totalCS.setBorderTop(BorderStyle.THIN);        //设置上边框
        totalCS.setBorderLeft(BorderStyle.THIN);       //设置左边框
        totalCS.setBorderRight(BorderStyle.THIN);      //设置右边框
        totalCS.setWrapText(true); // 设置自动换行显示
        totalCS.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 设置背景填充色
//        totalCS.setFillForegroundColor(new XSSFColor(new Color(221, 235, 247)));
        return totalCS;
    }


    /**
     * @param workbook
     * @param localPath 全名包括文件名
     * @return
     */
    public static boolean saveExcelFile(XSSFWorkbook workbook, String localPath) {
        try {
            FileOutputStream fout = new FileOutputStream(localPath);
            workbook.write(fout);
            workbook.close();
            fout.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }


    /**
     * @ClaseName: ExcelImportUtils
     * @Description: 根据文件路径获取Workbook
     * @Author: dongwh
     * @Param filePath 文件全路径
     * @return: Workbook
     */
    public static Workbook getWorkbook(String filePath) throws IOException {
        String suffix = ".xlsx";
        filePath = URLEncoder.encode(filePath + "(" + System.currentTimeMillis() + ")" + suffix, "UTF-8");

        if (StringUtils.isBlank(filePath)) {
        }
        if (filePath.trim().toLowerCase().endsWith("xls")) {
            return new HSSFWorkbook(new FileInputStream(filePath));
        } else if (filePath.trim().toLowerCase().endsWith("xlsx")) {
            return new XSSFWorkbook(new FileInputStream(filePath));
        } else {
            return null;
        }
    }

    /**
     * @ClaseName: ExcelImportUtils
     * @Description: 根据文件获取Workbook
     * @Author: dongwh
     * @Date: 2021/7/20 11:12
     * @Param file 文件
     * @return: Workbook
     */
    public static Workbook getWorkbookFromFile(File file) throws IOException {
        if (file.getName().endsWith("xls")) {
            return new HSSFWorkbook(new FileInputStream(file));
        } else if (file.getName().endsWith("xlsx")) {
            return new XSSFWorkbook(new FileInputStream(file));
        } else {
            return null;
        }
    }

    public static XSSFWorkbook getXSSFWorkbook(String path) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(path);
        return workbook;
    }

    public static XSSFWorkbook getXSSFWorkbook(InputStream inputStream) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        return workbook;
    }




    /**
     * @param fileName    文件名称
     * @param data        根据listMap导出数据集合至EXCEL
     * @param tittleArray Excel列标题
     * @param colunmArray 导出数据map中的key
     * @param excelVer    excel版本
     * @return void
     * @description 导出Excel
     * @author DongWH
     * @date 2020/3/17 13:32
     */
    public static void exptToExcel(String fileName, List<Map<String, Object>> data,
                                   String[] tittleArray, String[] colunmArray, Integer excelVer) throws Exception {
        //大于50万自动中断抛出异常
        if (data.size() > 50 * 10000) {
            throw new RuntimeException("数据单次导出大于50万");
        }
        String suffix = null == excelVer || excelVer == 7 ? ".xlsx" : ".xls";
        Workbook workbook = null;
        if (excelVer == null) {
            workbook = new XSSFWorkbook();
        } else if (excelVer == 3) {
            workbook = new HSSFWorkbook();//使用2003版本office
        } else if (excelVer == 7) {
            workbook = new XSSFWorkbook();
        } else {
            workbook = new XSSFWorkbook();
        }
        Sheet sheet = workbook.createSheet(fileName);
        Row tittleRow = sheet.createRow(0);
        for (int i = 0; i < tittleArray.length; i++) {
            tittleRow.createCell(i).setCellValue(tittleArray[i]);
        }

        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(i + 1);
            Map<String, Object> map = data.get(i);
            for (int j = 0; j < colunmArray.length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(ObjectUtils.identityToString(map.get(colunmArray[j])));
            }
        }
        fileName = URLEncoder.encode(fileName + "(" + System.currentTimeMillis() + ")" + suffix, "UTF-8");
        OutputStream outputStream = null;
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
        } finally {
            if (outputStream != null) {
                outputStream.close();
            }
        }
    }

    /**
     * @ClaseName: ExcelImportUtils
     * @Description: 保存文件流
     * @Author: dongwh
     * @Date: 2021/7/20 11:15
     * @Param file
     * @return: String 保存后的地址
     */
    public static String saveToLocal(MultipartFile file, String tempPath) {
        String filePath = null;
        if (file != null) {
            try {
                String fileRealName = file.getOriginalFilename();//获得原始文件名;
                int pointIndex = fileRealName.lastIndexOf(".");//最后一个点号的位置
                String fileSuffix = fileRealName.substring(pointIndex);//截取文件后缀
                String fileName = fileRealName.substring(0, pointIndex);//截取文件名

                //避免文件名重复，新文件名新增时间戳
                String newFileName = fileName + System.currentTimeMillis();
                String saveFileName = newFileName.concat(fileSuffix);//新文件完整名路径
                File path = new File(tempPath); //判断文件路径下的文件夹是否存在，不存在则创建
                if (!path.exists()) {
                    path.mkdirs();
                }
                File savedFile = new File(tempPath + File.separator + saveFileName);
                //文件存在则重新获取文件名重新创建
                while (savedFile.exists()) {
                    newFileName = fileName + new Date().getTime();//新文件名
                    saveFileName = newFileName.concat(fileSuffix);//新文件完整名路径
                    savedFile = new File(tempPath + File.separator + saveFileName);
                }
                boolean isCreateSuccess = savedFile.createNewFile(); // 是否创建文件成功
                if (isCreateSuccess) {//将文件写入
                    // 使用下面的jar包
                    FileUtils.copyInputStreamToFile(file.getInputStream(), savedFile);
                    filePath = savedFile.getPath();
                } else {
                    throw new Exception("创建文件失败");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            return filePath;
        }
        return filePath;
    }


    public static void main(String[] args) {
        createExcel(null,null,null);

    }

    /**
     * 返回文件地址
     *
     * @param statisticsVos
     * @param titileArr
     * @param cellArr
     * @return
     */
    public static String createExcel(List<Map<String, String>> statisticsVos, String[] titileArr, String[] cellArr) {
        String downloadPath = "D:/temp";
        String fymdhms14 = "yyyyMM";
        String sdfStr = "yyyyMMddHHmm";
        SimpleDateFormat ymdhms = new SimpleDateFormat(sdfStr);
        SimpleDateFormat ymh = new SimpleDateFormat(fymdhms14);
        String dateStr = ymdhms.format(new Date());
        String parentPath = ymh.format(new Date());

        try {
            int rowNum = 0;
            XSSFWorkbook xssfWorkbook = ExceltUtils.createXSSFWorkbook();
            XSSFSheet sheel = ExceltUtils.createSheel(xssfWorkbook, "统计分析" + dateStr);
            ExceltUtils.createTitleRow(sheel, rowNum, titileArr);
            ExceltUtils.createDataRow(sheel,1, cellArr, statisticsVos);
            String path = downloadPath + File.separator + parentPath + File.separator + dateStr + ".xlsx";
            File excelFile = new File(path);
            File parentFile = excelFile.getParentFile();
            if (!parentFile.exists()) {
                parentFile.mkdirs();
            }
            if (excelFile.exists()) {
                excelFile.delete();
            }
            excelFile.createNewFile();
            ExceltUtils.saveExcelFile(xssfWorkbook, path);
            return path;
        } catch (IOException e) {
            log.error(" createExcel()异常：--> 异常：{}", e);
        }
        return null;
    }

    /**
     * 1、将单元格的内容转换为字符串
     *
     * @param cell 单元格
     * @return 返回转换后的字符串
     */
    private static String convertCellValueToString(Cell cell) {
        //1.1、判断单元格的数据是否为空
        if (cell == null) {
            return null;
        }
        //1.2、设置单元格数据的初始值
        String cellValue = null;
        //1.3、获取单元格数据的类型
        switch (cell.getCellType()) {
            case NUMERIC:
                //1.3.1、获取到单元格数据的格式
                short dataFormat = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //1.3.1.1、处理日期格式，根据不同日期长度去判断
                    switch (dataFormat) {
                        case 14:
                            sdf = new SimpleDateFormat("yyyy/MM/dd");
                            break;
                        case 21:
                            sdf = new SimpleDateFormat("HH:mm:ss");
                            break;
                        case 22:
                            sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                            break;
                    }
                    //1.3.1.2、处理时间格式
                    Date date = cell.getDateCellValue();
                    assert sdf != null;
                    cellValue = sdf.format(date);
                } else if (dataFormat == 0) {
                    //1.3.2、处理普通数字格式
                    DecimalFormat format = new DecimalFormat("0");
                    double numericCellValue = cell.getNumericCellValue();
                    cellValue = format.format(numericCellValue);
                }
                break;
            case STRING:
            case BLANK:
                //处理字符串类型
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                //处理布尔类型
                boolean booleanCellValue = cell.getBooleanCellValue();
                cellValue = Boolean.toString(booleanCellValue);
                break;
            case FORMULA:
                //处理函数类型
                cellValue = cell.getCellFormula();
                break;
            case ERROR:
                byte errorCellValue = cell.getErrorCellValue();
                cellValue = Byte.toString(errorCellValue);
                break;
            default:
                break;
        }
        return cellValue;
    }

}